package com.XiGuaXiaoMe.dao;

import com.XiGuaXiaoMe.entity.Users;
import com.XiGuaXiaoMe.entity.course;
import com.XiGuaXiaoMe.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class courseDao {
    private JdbcUtil util=new JdbcUtil();
    public static final String sql_select = "select * from course;";//查询所有的sql语句
    public static final String sql_selectfuz = "select * from course where xuhao=? or....";//fuzzy query模糊查询
    public static final String sql_selectone = "select * from course where xuhao=?";//查询某一条课程
    public static final String sql_update = "update course set name=?,teacher=?,time=?," +
            "place=?,rongliang=?,xuhao=? where id=?;";//修改一条课程信息
    public static final String sql_delete = "delete from course where xuhao=?;";//删除一条课程信息
    public static final String sql_insert = "insert into course(id,name,teacher,time,place,rongliang,xuhao) values(?,?,?,?,?,?,?);";//添加一条课程信息



    //查询所有课程
    public List findAll()
    {
        String sql="select * from course";
        PreparedStatement ps=util.createStatement(sql);
        ResultSet rs=null;
        List courseList=new ArrayList();
        try {
            rs=ps.executeQuery();
            while(rs.next())
            {
                Integer id=rs.getInt("id");
                Integer rongliang=rs.getInt("rongliang");
                Integer xuhao=rs.getInt("xuhao");
                String name=rs.getString("name");
                String teacher=rs.getString("teacher");
                String time=rs.getString("time");
                String place=rs.getString("place");

                course courses=new course(id,name,teacher,time,place,rongliang,xuhao);
                courseList.add(courses);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.close(rs);
        }
        return courseList;
    }
    public List findAll2(String teacher)
    {
        String sql="select * from course where teacher=?";
        ResultSet rs=null;
        List courseList=new ArrayList();
        try {
            PreparedStatement pst=util.createStatement( sql);
            pst.setString(1,teacher );


            rs=pst.executeQuery();
            while(rs.next())
            {
                Integer id=rs.getInt("id");
                Integer rongliang=rs.getInt("rongliang");
                Integer xuhao=rs.getInt("xuhao");
                String name=rs.getString("name");
                teacher=rs.getString("teacher");
                String time=rs.getString("time");
                String place=rs.getString("place");

                System.out.println(id+" "+name+" "+teacher+" teacher!!");

                course courses=new course(id,name,teacher,time,place,rongliang,xuhao);
                courseList.add(courses);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.close(rs);
        }
        return courseList;
    }
    public List findAll3(String name)
    {
        String sql="select * from course where name=?";
        ResultSet rs=null;
        List courseList=new ArrayList();
        try {
            PreparedStatement pst=util.createStatement( sql);
            pst.setString(1,name );


            rs=pst.executeQuery();
            while(rs.next())
            {
                Integer id=rs.getInt("id");
                Integer rongliang=rs.getInt("rongliang");
                Integer xuhao=rs.getInt("xuhao");
                name=rs.getString("name");
                String teacher=rs.getString("teacher");
                String time=rs.getString("time");
                String place=rs.getString("place");

                course courses=new course(id,name,teacher,time,place,rongliang,xuhao);
                System.out.println(id+" "+name+" "+teacher);
                courseList.add(courses);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.close(rs);
        }
        return courseList;
    }
    /*
     * 查询某条课程信息
     * */
    public course selectCou(int xuhao){
        course cou = null;
        try{
            PreparedStatement pst=util.createStatement( sql_selectone);
            pst.setInt(1, xuhao);
            ResultSet rs = pst.executeQuery();
            if(rs.next()){
                cou = new course(rs.getInt("id"),rs.getString("name"),
                        rs.getString("teacher"),rs.getString("time"),
                        rs.getString("place"),rs.getInt("rongliang"),
                        rs.getInt("xuhao"));
            }
        }catch(Exception e){e.printStackTrace();}
        return cou;
    }
    /*
     * 查询某条课程信息（模糊查询）
     * */
    public Iterator<course> selectCou(String aa){//aa作为模糊查询的关键字
        List<course> list = null;
        Iterator<course> listall = null;
        try{
            PreparedStatement pst=util.createStatement( sql_selectone);
            ResultSet rs = pst.executeQuery();
            list = new ArrayList<course>();
            while(rs.next()){
                course cou = new course(rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("teacher"),
                        rs.getString("time"),
                        rs.getString("place"),
                        rs.getInt("rongliang"),
                        rs.getInt("xuhao"));

                list.add(cou);
            }
            listall = list.iterator();
        }catch(Exception e){e.printStackTrace();}
        return listall;
    }
    /*
     * 修改某条课程信息
     * */
    public int updateCou(course cou){
        int rs = 0;
        try{

            PreparedStatement pst=util.createStatement( sql_update);

            pst.setString(1, cou.getName());
            pst.setString(2, cou.getTeacher());
            pst.setString(3, cou.getTime());
            pst.setString(4, cou.getPlace());
            pst.setInt(5, cou.getRongliang());
            pst.setInt(6, cou.getXuhao());
            pst.setInt(7, cou.getId());
            rs = pst.executeUpdate();
            if(rs!=0){
                System.out.println("course_xuhao"+cou.getXuhao()+"update over!");
            }
        }catch(Exception e){e.printStackTrace();}
        return rs;
    }
    /*
     * 删除某条课程信息
     * */
    public int deleteCou(int xuhao){
        int rs = 0;
        try{

            PreparedStatement pst=util.createStatement( sql_delete);
            pst.setInt(1, xuhao);
            rs = pst.executeUpdate();
            if(rs!=0){
                System.out.println("course_xuhao"+xuhao+"delete over!");
            }
        }catch(Exception e){e.printStackTrace();}
        return rs;
    }
    /*
     * 添加一条课程信息
     * */
    public int insertCou(course cou){
        int rs = 0;
        try{
            PreparedStatement pst=util.createStatement( sql_insert);
            pst.setInt(1, cou.getId());
            pst.setString(2, cou.getName());
            pst.setString(3, cou.getTeacher());
            pst.setString(4, cou.getTime());
            pst.setString(5, cou.getPlace());
            System.out.println(cou.getPlace());
            pst.setInt(6, cou.getRongliang());
            System.out.println(cou.getRongliang());
            pst.setInt(7, cou.getXuhao());
            System.out.println(cou.getXuhao());
            rs = pst.executeUpdate();
            if(rs!=0){
                System.out.println("course_xuhao"+cou.getXuhao()+"insert over!");
            }
        }catch(Exception e){e.printStackTrace();}
        return rs;
    }

}
